# -*- coding: utf-8 -*- 

import sys

from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtCore import *
from PySide6.QtSql import QSqlDatabase

from model.Util_SqlQuery import SqlQueryClass
from model.Util_SqlQueryModel import SqlQueryModelClass
from model.ItemQDialog import ItemFormClass
from model.Save_ItemQDialog import SaveItemFormClass
from openpyxl import Workbook,load_workbook

import os
os.chdir(os.path.dirname(__file__))

ID, FILEID, DATE, TITLE, NUM, TYPE, UNIT, FILE, SEC = range(9)


class SaveDataViewClass(QTextBrowser):

    def __init__(self):
        super().__init__()
        self.setWindowTitle("数据展示视图-只读")
        self.resize(750, 300)
        
        #导入sql工具类
        self.sqlUtil = SqlQueryClass()
        self.sqlUtil.tableName = "save_file"
        self.itemForm = SaveItemFormClass()
        
        
        # 表头数据
        self.headerTitleList = {
            "id" : "标识",
            "file_id" : "件      号",
            "year" : "归档年度",
            "term" : "保管期限",
            "title" : "文件题名",
            "box_id" : "盒      号",
            "page" : "页      数",
            "file_type" : "文件类型",
            "government_unit" : "责 任 者",
            "date" : "文件时间",
            "file_number" : "文件编号",
            "src" : "文件路径"
        }
        # 创建窗口
        self.createWindow()
        # 设置表格
        self.setTableView()
        
        # 信号槽连接
        self.firstButton.clicked.connect(self.onFirstButtonClick)
        self.prevButton.clicked.connect(self.onPrevButtonClick)
        self.nextButton.clicked.connect(self.onNextButtonClick)
        self.lastButton.clicked.connect(self.onLastButtonClick)
        self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick)
        self.selectDateButton.clicked.connect(self.queryByFileId)

        self.selectByDataDateButton.clicked.connect(self.queryByDate)
        self.selectByTitleDateButton.clicked.connect(self.queryByLikeFitle)
        self.combobox_addData.activated.connect(lambda x: self.setPageRecordCount(self.combobox_addData.itemText(x)))
        self.itemForm.accepted.connect(self.itemFormAccepted)

        # 上下文菜单
        self.menu = self.generateMenu()
        self.tableView.setContextMenuPolicy(Qt.CustomContextMenu)  ######允许右键产生子菜单
        self.tableView.customContextMenuRequested.connect(self.showMenu)  ####右键菜单
        #self.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
        
        # 表格高度固定值
        self.tableView.verticalHeader().setSectionResizeMode(QHeaderView.Fixed)
        # 设置所有行的高度为固定值，比如30像素
        for row in range(self.tableView.model().rowCount()):
            self.tableView.verticalHeader().resizeSection(row, 40)
        
                
    # 创建窗口
    def createWindow(self):
        # 设置组件
        operatorLayout = QHBoxLayout()
        self.prevButton = QPushButton("前一页")
        self.nextButton = QPushButton("后一页")
        self.firstButton = QPushButton("第一页")
        self.lastButton = QPushButton("最后一页")
        
        kongLabel = QLabel("")
        kongLabel.setFixedWidth(1000)
        
        showPage = QLabel("每页显示")
        self.combobox_addData = QComboBox(minimumWidth=10)
        self.combobox_addData.addItems(["10","50","100"])
        self.combobox_addData.setFixedWidth(50)
        row = QLabel("条")
        
        switchPage = QLabel("转到第")
        self.switchPageLineEdit = QLineEdit()
        self.switchPageLineEdit.setValidator(QIntValidator(self))
        self.switchPageLineEdit.setFixedWidth(30)
        page = QLabel("页")
        self.switchPageButton = QPushButton("Go")        

        #添加布局
        operatorLayout.addWidget(self.firstButton)
        operatorLayout.addWidget(self.prevButton)
        operatorLayout.addWidget(self.nextButton)
        operatorLayout.addWidget(self.lastButton)
        operatorLayout.addWidget(kongLabel)
        operatorLayout.addWidget(showPage)
        operatorLayout.addWidget(self.combobox_addData)
        operatorLayout.addWidget(row)
        
        operatorLayout.addWidget(switchPage)
        operatorLayout.addWidget(self.switchPageLineEdit)
        operatorLayout.addWidget(page)
        operatorLayout.addWidget(self.switchPageButton)
        operatorLayout.addWidget(QSplitter())
        
        ### 查询布局
        selectLayout = QHBoxLayout()
        
        selectLabel = QLabel("  按列名查询：")
        selectLabel.setFixedWidth(80)

        self.field_addData = QComboBox(minimumWidth=30)
        self.field_addData.addItems(list(self.headerTitleList.values()))
        
        self.fieldLineEdit = QLineEdit()
        self.fieldLineEdit.setFixedWidth(100)
        
        self.selectDateButton = QPushButton("查询")
        
        selectTitleLabel = QLabel("按文件标题关键字查询：")
        selectTitleLabel.setFixedWidth(130)
        
        self.titleLineEdit = QLineEdit()
        self.titleLineEdit.setFixedWidth(200)
        
        self.selectByTitleDateButton = QPushButton("搜索")
        
        selectDateLabel = QLabel("  按文件日期范围查询：")
        selectDateLabel.setFixedWidth(120)
        
        # 创建开始日期和结束日期的QDateEdit控件
        self.start_date_edit = QDateEdit(self)
        self.start_date_edit.setCalendarPopup(True)
        self.start_date_edit.setDate(QDate.currentDate().addDays(-7))# 默认设置为当前日期前7天
        self.start_date_edit.setDisplayFormat('yyyy-MM-dd')
        self.start_date_edit.setFixedWidth(90)
        
        self.end_date_edit = QDateEdit(self)
        self.end_date_edit.setCalendarPopup(True)
        self.end_date_edit.setDate(QDate.currentDate())  
        self.end_date_edit.setDisplayFormat('yyyy-MM-dd')
        self.end_date_edit.setFixedWidth(90)
        
        self.selectByDataDateButton = QPushButton("搜索")
        
        selectLayout.addWidget(selectLabel)
        selectLayout.addWidget(self.field_addData)
        selectLayout.addWidget(self.fieldLineEdit)
        selectLayout.addWidget(self.selectDateButton)      
        selectLayout.addWidget(selectTitleLabel)        
        selectLayout.addWidget(self.titleLineEdit)        
        selectLayout.addWidget(self.selectByTitleDateButton)
        selectLayout.addWidget(selectDateLabel)
        selectLayout.addWidget(self.start_date_edit)
        selectLayout.addWidget(QLabel("  至  "))
        selectLayout.addWidget(self.end_date_edit)
        selectLayout.addWidget(self.selectByDataDateButton)     
        selectLayout.addWidget(QSplitter())       

        # 状态布局
        statusLayout = QHBoxLayout()
        self.totalPageLabel = QLabel()
        self.totalPageLabel.setFixedWidth(70)
        self.currentPageLabel = QLabel()
        self.currentPageLabel.setFixedWidth(70)
        self.totalRecordLabel = QLabel()
        self.totalRecordLabel.setFixedWidth(70)

        statusLayout.addWidget(self.totalPageLabel)
        statusLayout.addWidget(self.currentPageLabel)
        statusLayout.addWidget(QSplitter())
        statusLayout.addWidget(self.totalRecordLabel)

        # 设置表格属性
        self.tableView = QTableView()
        # 表格宽度的自适应调整
        self.tableView.horizontalHeader().setStretchLastSection(True)
        #self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
        # 表格高度的自适应调整
        #self.tableView.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        self.tableView.setStyleSheet("QTableView QTableCornerButton::section,QHeaderView::section { height: 50px;}")

        
        # 创建界面
        mainLayout = QVBoxLayout(self)
        mainLayout.addLayout(selectLayout)
        mainLayout.addWidget(self.tableView)
        mainLayout.addLayout(operatorLayout)
        mainLayout.addLayout(statusLayout)
        self.setLayout(mainLayout)

    # 设置表格
    def setTableView(self):

        # 声明查询模型
        self.queryModel = SqlQueryModelClass()
        
        # 设置数据库表名
        self.queryModel.tableName = 'save_file'
        
        # 设置模型
        self.tableView.setModel(self.queryModel)
        
        # 显示首页数据
        self.PageRecordCount = 10
        self.onFirstButtonClick()
        
        # 刷新状态
        self.updateStatus()

        # 设置表头
        self.queryModel.setHeaderDatas(list(self.headerTitleList.values()))

    # 设置上下文菜单
    def generateMenu(self):
        menu = QMenu(self)
        menu.addAction(QIcon("./images/up.png"), '第一页', self.onFirstButtonClick, QKeySequence(Qt.CTRL | Qt.Key_F))
        menu.addAction(QIcon("./images/left.png"), '前一页', self.onPrevButtonClick, QKeySequence(Qt.CTRL | Qt.Key_P))
        menu.addAction(QIcon("./images/right.png"), '后一页', self.onNextButtonClick, QKeySequence(Qt.CTRL | Qt.Key_N))
        menu.addAction(QIcon("./images/down.png"), '最后一页', self.onLastButtonClick, QKeySequence(Qt.CTRL | Qt.Key_L))
        menu.addSeparator()
        menu.addAction('全选', lambda: self.tableView.selectAll(), QKeySequence(Qt.CTRL | Qt.Key_A))
        menu.addAction('选择行', lambda: self.tableView.selectRow(self.tableView.currentIndex().row()),
                       QKeySequence(Qt.CTRL | Qt.Key_R))
        menu.addAction('选择列', lambda: self.tableView.selectColumn(self.tableView.currentIndex().column()),
                       QKeySequence(Qt.CTRL | Qt.SHIFT | Qt.Key_R))
        menu.addAction('打开',self.openFileDir,
            QKeySequence(Qt.CTRL | Qt.SHIFT | Qt.Key_O))
        menu.addAction('修改',self.updateRowDate,
            QKeySequence(Qt.CTRL | Qt.SHIFT | Qt.Key_O))
        menu.addAction('删除行',lambda: self.delRowDate(self.queryModel.record(self.tableView.currentIndex().row()).value(list(self.headerTitleList.keys()).index('id'))),
            QKeySequence(Qt.CTRL | Qt.SHIFT | Qt.Key_O))
        menu.addAction('序号重排',lambda: self.newSetAllfileId(),
            QKeySequence(Qt.CTRL | Qt.SHIFT | Qt.Key_O))
        menu.addAction('导出本页',lambda: self.exportFile())
        menu.addAction('导入',lambda: self.importFile())
        menu.addAction('下载模版',lambda: self.downloadImportFile())
        
        return menu

    def showMenu(self, pos):
        self.menu.exec(QCursor.pos())  # 显示菜单

    def openFileDir(self):
        if self.tableView.currentIndex().row() >= 0:
            fileName = self.queryModel.record(self.tableView.currentIndex().row()).value(list(self.headerTitleList.keys()).index('src'))
            print(fileName)
            os.startfile(os.path.dirname(fileName))
            
    #导出当前表格数据
    def exportFile(self):
        #创建工作表
        sql_model = self.queryModel
        wb = Workbook()
        ws = wb.active
        ws.append(list(self.headerTitleList.values()))

        # 获取当前表格数据
        for row in range(sql_model.rowCount()):
            rowlist = []
            for col in range(sql_model.columnCount()):
                index = sql_model.index(row, col)
                value = sql_model.data(index)
                if value is not None:
                    rowlist.append(value)
            ws.append(rowlist)        
        wb.save(f"save_file--第{self.currentPage}页.xlsx")
        #打开文件夹
        os.startfile(os.path.dirname(f"save_file--第{self.currentPage}页.xlsx"))
        
    #导入表格数据
    def importFile(self): 
        # 使用QFileDialog打开文件对话框
        options = QFileDialog.Options()
        options |= QFileDialog.DontUseNativeDialog
        fileName, _ = QFileDialog.getOpenFileName(self, "选择文件", "",
                                                 "Text Files (*.xlsx);;All Files (*)", options=options)
        if fileName=="":
            QMessageBox.information(self, "提示", "请选择xlsx文件！")
            return
        
        # 加载工作簿
        workbook = load_workbook(fileName)

        # 选择工作表，这里使用第一个工作表作为示例
        worksheet = workbook.active  # 如果你想指定工作表名，请使用 workbook['Sheet1']

        # 遍历工作表中的每一行
        for row in worksheet.iter_rows(min_row=2, values_only=True):
            # 过滤掉空行（只包含None值的行）
            if all(cell is None for cell in row):
                continue
            keys = list(self.headerTitleList.keys())
            # 使用zip函数和字典推导式来组合它们
            my_dict = dict(zip(keys, list(row)))
            self.sqlUtil.insertOneItemToSave_file_table(my_dict)

        # 关闭工作簿（可选，但推荐在不再需要时关闭）
        workbook.close()
        QMessageBox.information(self, "提示", "数据导入完成！")
        self.onFirstButtonClick()
        
    #下载模版
    def downloadImportFile(self):
        wb = Workbook()
        ws = wb.active
        ws.append(list(self.headerTitleList.values()))
        wb.save("save_file导入模版.xlsx")
        #打开文件夹
        os.startfile(os.path.dirname("save_file导入模版.xlsx"))
                
    # 刷新状态
    def updateStatus(self):
        szCurrentText = "当前第%d页" % self.currentPage
        self.currentPageLabel.setText(szCurrentText)


        # 设置按钮是否可用
        if self.totalPage==1:
            self._extracted_from_updateStatus_8(False, False)
        elif self.currentPage == 1:
            self._extracted_from_updateStatus_8(False, True)
        elif self.currentPage >= self.totalPage:
            self._extracted_from_updateStatus_8(True, False)
        else:
            self._extracted_from_updateStatus_8(True, True)

    # TODO Rename this here and in `updateStatus`
    def _extracted_from_updateStatus_8(self, arg0, arg1):
        self.firstButton.setEnabled(arg0)
        self.prevButton.setEnabled(arg0)
        self.nextButton.setEnabled(arg1)
        self.lastButton.setEnabled(arg1)
    
    # 第一页按钮按下
    def onFirstButtonClick(self):
        print('*** onFirstButtonClick ')
        # 得到总记录数
        self.totalRecrodCount = self.sqlUtil.getTotalRecordCount()
        # 得到总页数
        self.totalPage = int(self.totalRecrodCount / self.PageRecordCount) if (self.totalRecrodCount % self.PageRecordCount == 0) else int((self.totalRecrodCount / self.PageRecordCount) + 1)
        # 设置总页数文本
        self.totalPageLabel.setText("总共%d页" % self.totalPage)
        # 设置总记录数
        self.totalRecordLabel.setText("共%d条" % self.totalRecrodCount)
        
        self.queryModel.recordQueryByDataList(list(self.headerTitleList.keys()), 0, self.PageRecordCount)
        self.currentPage = 1
        self.updateStatus()
        # 设置所有行的高度为固定值，比如30像素
        for row in range(self.tableView.model().rowCount()):
            self.tableView.verticalHeader().resizeSection(row, 40)
          
    # 前一页按钮按下
    def onPrevButtonClick(self):
        print('*** onPrevButtonClick ')
        limitIndex = (self.currentPage - 2) * self.PageRecordCount
        self.queryModel.recordQueryByDataList(list(self.headerTitleList.keys()), limitIndex, self.PageRecordCount)
        self.currentPage -= 1
        self.updateStatus()

    # 后一页按钮按下
    def onNextButtonClick(self):
        print('*** onNextButtonClick ')
        limitIndex = self.currentPage * self.PageRecordCount
        self.queryModel.recordQueryByDataList(list(self.headerTitleList.keys()), limitIndex, self.PageRecordCount)
        self.currentPage += 1
        self.updateStatus()

    # 最后一页按钮按下
    def onLastButtonClick(self):
        print('*** onLastButtonClick ')
        limitIndex = (self.totalPage - 1) * self.PageRecordCount
        self.queryModel.recordQueryByDataList(list(self.headerTitleList.keys()), limitIndex, self.PageRecordCount)
        self.currentPage = self.totalPage
        self.updateStatus()

    # 转到页按钮按下
    def onSwitchPageButtonClick(self):
        # 得到输入字符串
        szText = self.switchPageLineEdit.text()

        # 是否为空
        if szText == '':
            QMessageBox.information(self, "提示", "请输入跳转页面")
            return

        # 得到页数
        pageIndex = int(szText)
        
        # 判断是否有指定页
        if pageIndex > self.totalPage or pageIndex < 1:
            QMessageBox.information(self, "提示", "没有指定的页面，请重新输入")
            return

        # 得到查询起始行号
        limitIndex = (pageIndex - 1) * self.PageRecordCount

        # 记录查询
        self.queryModel.recordQueryByDataList(list(self.headerTitleList.keys()), limitIndex, self.PageRecordCount)
        
        # 设置当前页
        self.currentPage = pageIndex
        # 刷新状态
        self.updateStatus()
        
    # 设置每页记录数
    def setPageRecordCount(self, count):
        print('*** setPageRecordCount ')
        self.PageRecordCount = int(count)
        self.onFirstButtonClick()
    # 执行新增或修改操作
    def itemFormAccepted(self):
        if self.itemForm.itemType == "修改":
            self.sqlUtil.updateOneItemToSave_file_table(self.itemForm.getItems())
        self.itemForm.copy_file()
        self.onFirstButtonClick()
    # 修改数据    
    def updateRowDate(self):
        indexList = self.tableView.selectedIndexes()
        try:
            indexList[1].row()
        except:
            QMessageBox.information(self, "提示", "请选择行")
            return

        keys = list(self.headerTitleList.keys())
        print(keys)
        # 使用zip函数和字典推导式来组合它们
        my_dict = dict(zip(keys, list(map(lambda x: x.data(), indexList))))
        print(my_dict)
        self.itemForm.ItemQDialogTitle("修改数据")
        self.itemForm.itemType = "修改"
        self.itemForm.setSaveItems(my_dict)
        self.itemForm.id = my_dict['id']
        self.itemForm.exec()  # 模态显示对话框
        
    # 删除数据    
    def delRowDate(self,id):
        self.sqlUtil.deleteOneItem(id)
        self.onFirstButtonClick()
    
    #序号重排
    def newSetAllfileId(self):
        year="2024"
        self.sqlUtil.updateAllfileId(year)
        self.onFirstButtonClick()
    
    #=======================================================================================#
    ###
    ######## 1.查询数据
    
    #按某个字段值查询数据
    def queryByFileId(self):
        
        filedList = list(self.headerTitleList.keys())
        fieldText = self.fieldLineEdit.text()
        index = self.field_addData.currentIndex()
        if fieldText == '' and index == -1:
            QMessageBox.information(self, "提示", "请输入查询条件")
            return
        self.queryModel.queryByFileId(filedList[index],fieldText)
    ###
    #=======================================================================================#
    
    #=======================================================================================#
    ###
    ######## 3.按标题关键字查询数据
    def queryByLikeFitle(self):
        title_key = self.titleLineEdit.text()
        if title_key == '':
            QMessageBox.information(self, "提示", "请输入搜索条件")
            return
        self.queryModel.queryByLikeFitle(title_key)
    ###
    #=======================================================================================#
    #=======================================================================================#
    ###
    ######## 4.根据日期范围查询接收文件记录
    #SELECT * FROM your_table_name WHERE date >= '2023-01-01' AND date <= '2024-12-31';
    
    def queryByDate(self):
        start_date = self.start_date_edit.date().toString('yyyy-MM-dd')
        end_date = self.end_date_edit.date().toString('yyyy-MM-dd')
        if start_date > end_date:
            QMessageBox.information(self, "提示", "开始日期不能大于结束日期")
            return
        self.queryModel.queryByDate(start_date, end_date)
    ###
    #=======================================================================================#
    
       
if __name__ == '__main__':

    app = QApplication(sys.argv)

    # 打开数据库，该库由qt_createSql.py脚本创建。
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('../db/filedatabase.db')
    if db.open() is not True:
        QMessageBox.critical(QWidget, 'open error', '数据库打开失败')
        exit()
    main_win = SaveDataViewClass()
    main_win.show()
    sys.exit(app.exec())